# print(jim.replace('\n',',').split(',')[1:-1])
# dicts = {}
# for i in jim.replace('\n',',').split(',')[1:-1]:
#     nums = {i:i}
#     dicts.update(nums)
# print(dicts)
# print(len(dicts))




import time

import httpx
import requests
import json
import xlrd
import threadpool
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
# from xlutils.copy import copy
import openpyxl
import pymysql

# conn = pymysql.connect(host='192.168.2.205', port=3306, user='jinhuitao', password='jinhuitao_123456',
#                        database='sunway_db', charset='utf8')
# cursor = conn.cursor()
func_list_1 = []
c = 0
def func(ji):  # 请求产品的ID
    global c
    c +=1
    print(c)
    headers = {
        'Accept': 'application/json, text/javascript, */*; q=0.01',
        'Accept-Encoding': 'gzip, deflate, br',
        'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
        'Connection': 'keep-alive',
        # 'Content-Length': '380',
        'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        'Cookie': 'product_category_is_show=1; UM_distinctid=17c15b16b7275-02b55430879ff9-2343360-1fa400-17c15b16b7376d; _ga=GA1.2.368327117.1632464693; SID_a94cdd47df94bdf79824f206af938ffc=3e86e2943d2aa3f37252461a5c55a247; 15706_dashboard_list=%7B%22user-profile-wg%22%3A1%2C%22publish-version-wg%22%3A2%2C%22platform-item-listing-wg%22%3A3%7D; 15706_bulletin=%7B%22is_tip%22%3Atrue%2C%22data%22%3A%7B%22v_id%22%3A%22274%22%2C%22v_tip_content%22%3A%22%E4%BA%B2%E7%88%B1%E7%9A%84%E7%94%A8%E6%88%B7%EF%BC%8C%E4%B8%BA%E4%BA%86%E7%BB%99%E5%90%84%E4%BD%8D%E5%AE%A2%E6%88%B7%E6%8F%90%E4%BE%9B%E6%9B%B4%E5%8A%A0%E7%A8%B3%E5%AE%9A%E9%AB%98%E6%95%88%E7%9A%84%E7%BF%BB%E8%AF%91%E6%9C%8D%E5%8A%A1%EF%BC%8C%E6%88%91%E4%BB%AC%E5%86%B3%E5%AE%9A%E4%BA%8E%2012%E6%9C%8821%E6%97%A5%20%E8%B5%B7%E5%AF%B9%20%5C%22%E4%B8%80%E9%94%AE%E7%BF%BB%E8%AF%91%E2%80%9D%E6%8E%A5%E5%8F%A3%E8%BF%9B%E8%A1%8C%E5%8D%87%E7%BA%A7%EF%BC%8C%E6%8E%A5%E5%8F%A3%E5%8D%87%E7%BA%A7%E6%9C%9F%E9%97%B4%EF%BC%8C%E2%80%9C%E4%B8%80%E9%94%AE%E7%BF%BB%E8%AF%91%E2%80%9D%E6%9C%8D%E5%8A%A1%E5%B0%86%E6%9A%82%E5%81%9C%E4%BD%BF%E7%94%A8%EF%BC%8C%E6%81%A2%E5%A4%8D%E6%97%B6%E9%97%B4%E5%B0%86%E5%8F%A6%E8%A1%8C%E9%80%9A%E7%9F%A5%E3%80%82%E5%A6%82%E6%9C%89%E4%BB%BB%E4%BD%95%E4%B8%8D%E4%BE%BF%E4%B9%8B%E5%A4%84%EF%BC%8C%E6%95%AC%E8%AF%B7%E8%B0%85%E8%A7%A3%EF%BC%81%22%2C%22v_tip_start_date%22%3A%222020-12-18%2000%3A00%3A00%22%2C%22v_tip_end_date%22%3A%222020-12-27%2000%3A00%3A00%22%7D%2C%22id%22%3A%22274%232020-12-18%2000%3A00%3A00%232020-12-27%2000%3A00%3A00%22%7D; _gid=GA1.2.1895761040.1646120025; LANGUAGE=zh_CN; currentPage=0%7B%7C%7D%2Fsystem%2Fhome%7B%7C%7D%E9%A6%96%E9%A1%B5; _gat=1; Hm_lvt_df17a76b1cc89f0e7c4dcc7df476cb95=1646120034,1646122752,1646181778,1646181809; Hm_lpvt_df17a76b1cc89f0e7c4dcc7df476cb95=1646181831',
        'Host': 'adv-listing.eccang.com',
        'Origin': 'https://adv-listing.eccang.com',
        'Referer': 'https://adv-listing.eccang.com/product/publish-product/list?menu=productManage',
        'sec-ch-ua': '" Not;A Brand";v="99", "Google Chrome";v="97", "Chromium";v="97"',
        'sec-ch-ua-mobile': '?0',
        'sec-ch-ua-platform': '"Windows"',
        'Sec-Fetch-Dest': 'empty',
        'Sec-Fetch-Mode': 'cors',
        'Sec-Fetch-Site': 'same-origin',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36',
        'X-Requested-With': 'XMLHttpRequest',

    }

    data = {

        'product_category_id': '',
        'publish_platform': '',
        'published': '1',
        'batch_type': '1',
        'skuType': '1',
        'productSku': '{}'.format(str(ji).replace('.0','')),
        'product_name_like': '',
        'publish_product_group_id': '',
        'product_status': '1',
        'erp_sale_status': '',
        'is_variant': '',
        'create_way': '',
        'is_push': '',
        'Product_platform': '',
        'batch_code': '',
        'create_id': '',
        'modify_id': '',
        'product_update_time_from': '',
        'product_update_time_to': '',
        'product_add_time_from': '',
        'product_add_time_to': '',
        'search_sort[1]': 'pp_id-desc',

    }
    url = 'https://adv-listing.eccang.com/product/publish-product/list/page/1/pageSize/20'
    response = requests.post(url, headers=headers, data=data, timeout=10).json()
    # print(response)
    # print(response['data'][0]['pp_id'])
    # print('第{}sku获取中'.format(str(ji).replace('.0','')))
    if str(response['state']) == '1':
        # print('id为：', response['data'][0]['pp_id'])
        # return response['data'][0]['pp_id']
        data_1 = response['data'][0]['pp_id'] + '*****' + str(ji).replace('.0','')
        print(data_1)
        func_list_1.append(data_1)
    else:
        sku, html_title, title, datas, words = str(ji).replace('.0',''), '', '', '', ''
        print(sku)
        mysql_(sku, html_title, title, datas, words)
        # ws.cell(row=count, column=1).value = "{}".format(ji)
        # wb.save(r"C:\Users\Administrator\Desktop\jimlucky3.xlsx")
        # return 'none'


def func1(list_all):
    print(list_all)
    id = list_all.split('*****')[0]
    ji = list_all.split('*****')[1]
    print('id为：', id)
    print('第{}sku获取中'.format(ji))
    headers = {

        'Accept': 'application/json, text/javascript, */*; q=0.01',
        'Accept-Encoding': 'gzip, deflate, br',
        'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
        'Connection': 'keep-alive',
        # 'Content-Length': '28',
        'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        'Cookie': 'product_category_is_show=1; UM_distinctid=17c15b16b7275-02b55430879ff9-2343360-1fa400-17c15b16b7376d; _ga=GA1.2.368327117.1632464693; SID_a94cdd47df94bdf79824f206af938ffc=3e86e2943d2aa3f37252461a5c55a247; currentPage=0%7B%7C%7D%2Fsystem%2Fhome%7B%7C%7D%E9%A6%96%E9%A1%B5; 15706_dashboard_list=%7B%22user-profile-wg%22%3A1%2C%22publish-version-wg%22%3A2%2C%22platform-item-listing-wg%22%3A3%7D; LANGUAGE=zh_CN; 15706_bulletin=%7B%22is_tip%22%3Atrue%2C%22data%22%3A%7B%22v_id%22%3A%22274%22%2C%22v_tip_content%22%3A%22%E4%BA%B2%E7%88%B1%E7%9A%84%E7%94%A8%E6%88%B7%EF%BC%8C%E4%B8%BA%E4%BA%86%E7%BB%99%E5%90%84%E4%BD%8D%E5%AE%A2%E6%88%B7%E6%8F%90%E4%BE%9B%E6%9B%B4%E5%8A%A0%E7%A8%B3%E5%AE%9A%E9%AB%98%E6%95%88%E7%9A%84%E7%BF%BB%E8%AF%91%E6%9C%8D%E5%8A%A1%EF%BC%8C%E6%88%91%E4%BB%AC%E5%86%B3%E5%AE%9A%E4%BA%8E%2012%E6%9C%8821%E6%97%A5%20%E8%B5%B7%E5%AF%B9%20%5C%22%E4%B8%80%E9%94%AE%E7%BF%BB%E8%AF%91%E2%80%9D%E6%8E%A5%E5%8F%A3%E8%BF%9B%E8%A1%8C%E5%8D%87%E7%BA%A7%EF%BC%8C%E6%8E%A5%E5%8F%A3%E5%8D%87%E7%BA%A7%E6%9C%9F%E9%97%B4%EF%BC%8C%E2%80%9C%E4%B8%80%E9%94%AE%E7%BF%BB%E8%AF%91%E2%80%9D%E6%9C%8D%E5%8A%A1%E5%B0%86%E6%9A%82%E5%81%9C%E4%BD%BF%E7%94%A8%EF%BC%8C%E6%81%A2%E5%A4%8D%E6%97%B6%E9%97%B4%E5%B0%86%E5%8F%A6%E8%A1%8C%E9%80%9A%E7%9F%A5%E3%80%82%E5%A6%82%E6%9C%89%E4%BB%BB%E4%BD%95%E4%B8%8D%E4%BE%BF%E4%B9%8B%E5%A4%84%EF%BC%8C%E6%95%AC%E8%AF%B7%E8%B0%85%E8%A7%A3%EF%BC%81%22%2C%22v_tip_start_date%22%3A%222020-12-18%2000%3A00%3A00%22%2C%22v_tip_end_date%22%3A%222020-12-27%2000%3A00%3A00%22%7D%2C%22id%22%3A%22274%232020-12-18%2000%3A00%3A00%232020-12-27%2000%3A00%3A00%22%7D; Hm_lvt_df17a76b1cc89f0e7c4dcc7df476cb95=1645086434,1645145249,1645751488,1646011423; Hm_lpvt_df17a76b1cc89f0e7c4dcc7df476cb95=1646011431',
        'Host': 'adv-listing.eccang.com',
        'Origin': 'https://adv-listing.eccang.com',
        'Referer': 'https://adv-listing.eccang.com/product/publish-product/list-box?paramid={}&tab=title_description&title={}+--+%E7%BC%96%E8%BE%91&menu=productManage'.format(
            id, str(ji).replace('.0','')),
        'sec-ch-ua': '" Not;A Brand";v="99", "Google Chrome";v="97", "Chromium";v="97"',
        'sec-ch-ua-mobile': '?0',
        'sec-ch-ua-platform': '"Windows"',
        'Sec-Fetch-Dest': 'empty',
        'Sec-Fetch-Mode': 'cors',
        'Sec-Fetch-Site': 'same-origin',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36',
        'X-Requested-With': 'XMLHttpRequest',

    }

    data = {
        'language_code': 'en',
        'pp_id': '{}'.format(id)
    }
    url = 'https://adv-listing.eccang.com/product/publish-product/get-language-data'
    if id != 'none':
        response = requests.post(url, headers=headers, data=data, timeout=10).json()
        # print(response)
        if response['data'] != []:
            description_super_html = response['data']['description_super_html']  # 超文本描述
            description_text = response['data']['description_text']  # 纯文本描述
            title_group_mid = response['data']['title_group_mid']  # 商品标题
            func2(id, ji, description_super_html, description_text, title_group_mid)
        else:
            description_super_html = ''  # 超文本描述
            description_text = ''  # 纯文本描述
            title_group_mid = ''  # 商品标题
            func2(id, ji, description_super_html, description_text, title_group_mid)
            # ws.cell(row=count, column=1).value = "{}".format(ji)
            # wb.save(r"C:\Users\Administrator\Desktop\jimlucky3.xlsx")


def func2(id, ji, description_super_html, description_text, title_group_mid):
    headers = {
        'Accept': 'application/json, text/javascript, */*; q=0.01',
        'Accept-Encoding': 'gzip, deflate, br',
        'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
        'Connection': 'keep-alive',
        # 'Content-Length': '21',
        'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        'Cookie': 'product_category_is_show=1; UM_distinctid=17c15b16b7275-02b55430879ff9-2343360-1fa400-17c15b16b7376d; _ga=GA1.2.368327117.1632464693; SID_a94cdd47df94bdf79824f206af938ffc=3e86e2943d2aa3f37252461a5c55a247; currentPage=0%7B%7C%7D%2Fsystem%2Fhome%7B%7C%7D%E9%A6%96%E9%A1%B5; 15706_dashboard_list=%7B%22user-profile-wg%22%3A1%2C%22publish-version-wg%22%3A2%2C%22platform-item-listing-wg%22%3A3%7D; LANGUAGE=zh_CN; 15706_bulletin=%7B%22is_tip%22%3Atrue%2C%22data%22%3A%7B%22v_id%22%3A%22274%22%2C%22v_tip_content%22%3A%22%E4%BA%B2%E7%88%B1%E7%9A%84%E7%94%A8%E6%88%B7%EF%BC%8C%E4%B8%BA%E4%BA%86%E7%BB%99%E5%90%84%E4%BD%8D%E5%AE%A2%E6%88%B7%E6%8F%90%E4%BE%9B%E6%9B%B4%E5%8A%A0%E7%A8%B3%E5%AE%9A%E9%AB%98%E6%95%88%E7%9A%84%E7%BF%BB%E8%AF%91%E6%9C%8D%E5%8A%A1%EF%BC%8C%E6%88%91%E4%BB%AC%E5%86%B3%E5%AE%9A%E4%BA%8E%2012%E6%9C%8821%E6%97%A5%20%E8%B5%B7%E5%AF%B9%20%5C%22%E4%B8%80%E9%94%AE%E7%BF%BB%E8%AF%91%E2%80%9D%E6%8E%A5%E5%8F%A3%E8%BF%9B%E8%A1%8C%E5%8D%87%E7%BA%A7%EF%BC%8C%E6%8E%A5%E5%8F%A3%E5%8D%87%E7%BA%A7%E6%9C%9F%E9%97%B4%EF%BC%8C%E2%80%9C%E4%B8%80%E9%94%AE%E7%BF%BB%E8%AF%91%E2%80%9D%E6%9C%8D%E5%8A%A1%E5%B0%86%E6%9A%82%E5%81%9C%E4%BD%BF%E7%94%A8%EF%BC%8C%E6%81%A2%E5%A4%8D%E6%97%B6%E9%97%B4%E5%B0%86%E5%8F%A6%E8%A1%8C%E9%80%9A%E7%9F%A5%E3%80%82%E5%A6%82%E6%9C%89%E4%BB%BB%E4%BD%95%E4%B8%8D%E4%BE%BF%E4%B9%8B%E5%A4%84%EF%BC%8C%E6%95%AC%E8%AF%B7%E8%B0%85%E8%A7%A3%EF%BC%81%22%2C%22v_tip_start_date%22%3A%222020-12-18%2000%3A00%3A00%22%2C%22v_tip_end_date%22%3A%222020-12-27%2000%3A00%3A00%22%7D%2C%22id%22%3A%22274%232020-12-18%2000%3A00%3A00%232020-12-27%2000%3A00%3A00%22%7D; Hm_lvt_df17a76b1cc89f0e7c4dcc7df476cb95=1645086434,1645145249,1645751488,1646011423; Hm_lpvt_df17a76b1cc89f0e7c4dcc7df476cb95=1646011431',
        'Host': 'adv-listing.eccang.com',
        'Origin': 'https://adv-listing.eccang.com',
        'Referer': 'https://adv-listing.eccang.com/product/publish-product/list-box?paramid={}&tab=ebay_product&title={}+--+%E7%BC%96%E8%BE%91&menu=productManage'.format(
            id, ji),
        'sec-ch-ua': '" Not;A Brand";v="99", "Google Chrome";v="97", "Chromium";v="97"',
        'sec-ch-ua-mobile': '?0',
        'sec-ch-ua-platform': '"Windows"',
        'Sec-Fetch-Dest': 'empty',
        'Sec-Fetch-Mode': 'cors',
        'Sec-Fetch-Site': 'same-origin',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36',
        'X-Requested-With': 'XMLHttpRequest',
    }
    data = {
        'site_id': '3',
        'pp_id': '{}'.format(id)
    }
    url = 'https://adv-listing.eccang.com/product/publish-product/get-ebay-site-data'
    response = requests.post(url, headers=headers, data=data, timeout=10).json()
    # print(response['data']['specifics'])
    # lists = ['Type','Brand','MPN','Dexterity','Suitable For','Material','Size','Main Colour','Bundle Listing','Country/Region of Manufacture','Custom Bundle','Bundle Description','Modified Item','Modification Description','Non-Domestic Product','Applicable Regions']
    dicts = {}
    try:
        for i in response['data']['specifics']:
            # print(i['name'],'***',i['value'])
            dict_everyone = {f"{i['name']}": f"{i['value']}".replace('\n', ' ').replace('\r',' ')}
            dicts.update(dict_everyone)
        # if i['name'] in lists:
        #     lists.remove(i['name'])
        sku, html_title, title, datas, words = ji, description_super_html, description_text, title_group_mid, json.dumps(dicts)
        mysql_(sku, html_title, title, datas, words)
    except Exception as g:
        sku, html_title, title, datas, words = ji, description_super_html, description_text, title_group_mid, ''
        mysql_(sku, html_title, title, datas, words)
        print('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&')
        print(g)
    print('*' * 30)

def excel_read():
    books = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\需补录新系统款号.xlsx")

    sheet = books.sheet_by_index(0)  # sheet_by_index(0)表示为ceshishujvku.xlsx中的第一个表单

    return sheet.col_values(colx=0)[1:]  # 4627





def mysql_(sku, html_title, title, datas, words):
    newtime = time.strftime('%Y-%m-%d %H:%M:%S')
    conn = pymysql.connect(host='192.168.2.205', port=3306, user='jinhuitao', password='jinhuitao_123456',
                           database='sunway_db', charset='utf8')
    cursor = conn.cursor()
    cursor.execute(
        "REPLACE into jin_linshi set newtime=%s,sku=%s,html_title=%s,title=%s,datas=%s,words=%s",
        [newtime, str(sku), str(html_title), str(title), str(datas), str(words)])

    conn.commit()


def run():

    task_pool_1 = threadpool.ThreadPool(50)
    # print('0000000000000000000000')
    requests_1 = threadpool.makeRequests(func,excel_read())
    for req_1 in requests_1:


        task_pool_1.putRequest(req_1)

    task_pool_1.wait()

    task_pool = threadpool.ThreadPool(50)  # 开50个线程
    requests1 = threadpool.makeRequests(func1, func_list_1)
    # self.threading_thread为请求代码所在函数，self.list_all所传参数，不支持元组类型
    # print(requests)
    for req in requests1:
        task_pool.putRequest(req)
    task_pool.wait()



if __name__ == '__main__':
    run()





